package com.aaa.sbms.dao;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

public interface CarDao {

    /**
     * 查询车辆
     * @return
     */
    @Select("select car.*,u.username from car left join users u on u.userid=car.userid")
    List<Map> cars();

    /**
     * 分页查询车辆
     * @return
     */
    @Select("<script>select c.*,u.username from car c left join users u on u.userid=c.userid\n" +
            "<where>" +
            "<if test='cartype!=null'>and c.cartype like '%${cartype}%'</if>" +
            "<if test='carplate!=null'>and c.carplate like '%${carplate}%'</if></where>" +
            "order by c.carid desc limit #{begin},#{pageSize}</script>")
    List<Map> carPage(Map map);

    /**
     * 查询车辆总数量
     * @return
     */
    @Select("<script>select count(*) from car c left join users u on u.userid=c.userid\n" +
            "<where>" +
            "<if test='cartype!=null'>and c.cartype like '%${cartype}%'</if>" +
            "<if test='carplate!=null'>and c.carplate like '%${carplate}%'</if></where></script>")
    int carPageCount(Map map);

    /**
     * 分页查询车辆审核记录
     * @param map
     * @return
     */
    @Select("<script>select c.*,u.username from caraudit c left join users u on u.userid=c.userid\n" +
            "<where>" +
            "<if test='cartype!=null'>and c.cartype like '%${cartype}%'</if>" +
            "<if test='carplate!=null'>and c.carplate like '%${carplate}%'</if></where>" +
            "order by c.id desc limit #{begin},#{pageSize}</script>")
    List<Map> carauditPage(Map map);

    /**
     * 查询车辆审核总数量
     * @param map
     * @return
     */
    @Select("<script>select count(*) from caraudit c left join users u on u.userid=c.userid\n" +
            "<where>" +
            "<if test='cartype!=null'>and c.cartype like '%${cartype}%'</if>" +
            "<if test='carplate!=null'>and c.carplate like '%${carplate}%'</if></where></script>")
    int carauditPageCount(Map map);

    /**
     * 分页查询车辆使用记录
     * @param map
     * @return
     */
    @Select("<script>select c.*,u.username from caruse c left join users u on u.userid=c.userid\n" +
            "<where>" +
            "<if test=\"auditid!=null and auditid!=''\">and c.auditid=#{auditid}</if>" +
            "<if test='carplate!=null'>and c.carplate like '%${carplate}%'</if></where>" +
            "order by c.id desc limit #{begin},#{pageSize}</script>")
    List<Map> carusePage(Map map);

    /**
     * 查询车辆使用记录总数量
     * @param map
     * @return
     */
    @Select("<script>select count(*) from caruse c left join users u on u.userid=c.userid\n" +
            "<where>" +
            "<if test=\"auditid!=null and auditid!=''\">and c.auditid=#{auditid}</if>" +
            "<if test='carplate!=null'>and c.carplate like '%${carplate}%'</if></where></script>")
    int carusePageCount(Map map);

    /**
     * 分页查询车辆维修记录
     * @param map
     * @return
     */
    @Select("<script>select cm.maintainid,cm.cost,c.*,date_format(cm.maintaintime,'%Y%m%d %h:%i:%s') maintaintime,u.username\n" +
            "from carmaintain cm left join users u on u.userid=cm.userid\n" +
            "left join car c on c.carid=cm.carid\n" +
            "<where>" +
            "<if test=\"carid!=null and carid!=''\">and cm.carid=#{carid}</if>" +
            "<if test='username!=null'>and u.username like '%${username}%'</if>" +
            "</where>order by maintaintime desc limit #{begin},#{pageSize}</script>")
    List<Map> maintainCarPage(Map map);

    /**
     * 查询车辆维修总数
     * @param map
     * @return
     */
    @Select("<script>select count(*)\n" +
            "from carmaintain cm left join users u on u.userid=cm.userid\n" +
            "left join car c on c.carid=cm.carid\n" +
            "<where>" +
            "<if test=\"carid!=null and carid!=''\">and cm.carid=#{carid}</if>" +
            "<if test='username!=null'>and u.username like '%${username}%'</if>" +
            "</where></script>")
    int maintainCarPageCount(Map map);

    /**
     * 分页查询车辆加油记录
     * @param map
     * @return
     */
    @Select("<script>select cf.id,cf.cost,c.*,date_format(cf.addtime,'%Y-%m-%d %H:%i:%s') addtime,u.username\n" +
            "from carrefuel cf left join users u on u.userid=cf.userid\n" +
            "left join car c on c.carid=cf.carid\n" +
            "<where>" +
            "<if test=\"carid!=null and carid!=''\">and cf.carid=#{carid}</if>" +
            "<if test='username!=null'>and u.username like '%${username}%'</if>" +
            "</where>order by addtime desc limit #{begin},#{pageSize}</script>")
    List<Map> carRefuelPage(Map map);

    /**
     * 查询车辆加油总数
     * @param map
     * @return
     */
    @Select("<script>select count(*)\n" +
            "from carrefuel cf left join users u on u.userid=cf.userid\n" +
            "left join car c on c.carid=cf.carid\n" +
            "<where>" +
            "<if test=\"carid!=null and carid!=''\">and cf.carid=#{carid}</if>" +
            "<if test='username!=null'>and u.username like '%${username}%'</if>" +
            "</where></script>")
    int carRefuelPageCount(Map map);

    /**
     * 查询一个车辆
     * @param carid
     * @return
     */
    @Select("select c.*,u.username from car c left join users u on u.userid=c.userid where c.carid=#{carid}")
    Map carOne(int carid);

    /**
     * 查询一个车辆审核记录
     * @param id
     * @return
     */
    @Select("select c.*,u.username from caraudit c left join users u on u.userid=c.userid where id=#{id}")
    Map carauditOne(int id);

    /**
     * 查询一个车辆使用记录
     * @param id
     * @return
     */
    @Select("select usereason,reject from caruse where id=#{id}")
    Map caruseOne(int id);

    /**
     * 添加车辆
     * @param map
     * @return
     */
    @Insert("insert into car values(null,#{cartype},#{carplate},#{number},#{oilmass},#{damagelevel},#{state},null)")
    int addCar(Map map);

    /**
     * 添加车辆申请
     * @param map
     * @return
     */
    @Insert("insert into caraudit(id,userid,carid,cartype,carplate,oilmass,damagelevel,usereason,auditstate)\n" +
            "values(null,#{userid},#{carid},#{cartype},#{carplate},#{oilmass},#{damagelevel},#{usereason},1)")
    int addCarAudit(Map map);

    /**
     * 添加借出后的车辆使用记录
     * @param map
     * @return
     */
    @Insert("insert into caruse(id,cartype,carplate,oilmass,damagelevel,starttime,usereason,userid,auditid) values(" +
            "null,#{cartype},#{carplate},#{oilmass},#{damagelevel},date_format(now(),'%Y-%m-%d %H:%i:%s'),#{usereason}," +
            "#{userid},#{id})")
    int addCarUseAfterLend(Map map);

    /**
     * 添加车辆维修记录
     * @param map
     * @return
     */
    @Insert("insert into carmaintain values(null,#{userid},#{cost},#{carid},now())")
    int addCarMaintain(Map map);

    /**
     * 添加车辆加油记录
     * @param map
     * @return
     */
    @Insert("insert into carrefuel values(null,#{userid},#{cost},#{carid},now())")
    int addCarRefuel(Map map);

    /**
     * 修改车辆信息
     * @param map
     * @return
     */
    @Update("update car set cartype=#{cartype},carplate=#{carplate},number=#{number},oilmass=#{oilmass},state=#{state},userid=#{userid}," +
            "damagelevel=#{damagelevel} where carid=#{carid}")
    int updateCar(Map map);

    /**
     * 修改车辆状态为使用中
     * @param map
     * @return
     */
    @Update("update car set state=2,userid=#{userid} where carid=#{carid}")
    int updateCarStateAsUsing(Map map);

    /**
     * 修改车辆状态为空闲
     * @param map
     * @return
     */
    @Update("update car set state=1,set userid=null where carid=#{carid}")
    int updateCarStateAsFree(Map map);

    /**
     * 修改车辆申请审核状态为通过
     * @param map
     * @return
     */
    @Update("update caraudit set starttime=date_format(now(),'%Y-%m-%d %H:%i:%s')," +
            "auditstate=3,audituserid=#{audituserid} where id=#{id}")
    int updateCarAuditAsPass(Map map);

    /**
     * 修改车辆申请审核状态为驳回
     * @param map
     * @return
     */
    @Update("update caraudit set auditstate=2,audituserid=#{audituserid} where id=#{id}")
    int updateCarAuditAsReject(Map map);

    /**
     * 删除车辆
     * @param carid
     * @return
     */
    @Delete("delete from car where carid=#{carid}")
    int deleteCar(int carid);
}
